TickerDefinition

V8 Message Definiton

TickerDefinition (internal only) records exist for all SpiderRock tickers including equity tickers (stocks and ETFs) as well as index tickers and synthetic tickers for future chains and option multihedge baskets.

METADATA

Attribute	Value
Topic	4335-product-definition
MLink Token	EquityDefinition
Product	SRAnalytics
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
ticker_at	enum - AssetType	PRI	'None'
ticker_ts	enum - TickerSrc	PRI	'None'
ticker_tk	VARCHAR(12)	PRI	''
securityID	INT		0	Security ID number from the source Vendor SR Feed
symbolType	enum - SymbolType		'None'
name	VARCHAR(72)		''	Symbol name
country	VARCHAR(2)		''	ISO Issuer Country Code
primaryCurrency	enum - Currency		'None'
rateCurve	enum - RateCurve		'None'
parValue	FLOAT		0	Security Parvalue
parValueCurrency	VARCHAR(3)		''	Security Parvalue currency
pointValue	FLOAT		0
pointCurrency	enum - Currency		'None'
primaryExch	enum - PrimaryExchange		'None'
altID	INT		0	Alt Security ID number
mic	VARCHAR(4)		''	ISO Market Identification Code
micSeg	VARCHAR(4)		''	ISO Market Indentification Segment Code
symbol	VARCHAR(12)		''	trading symbol wo dot notation
issueClass	VARCHAR(1)		''	issue class of stock symbol if no issue class field will be blank
sharesOutstanding	BIGINT		0	symbol shares outstanding represented in thousands actualsharesoutstanding sharesoutstanding 1000
cusip	VARCHAR(10)		''	cusip code
indNum	INT		0	IND 2 digits
subNum	INT		0	SUB 4 digits
grpNum	INT		0	GRP 6 digits
nbrNum	INT		0	NBR 8 digits
sic	VARCHAR(4)		''	SIC Standard Industrial Classification code
cik	VARCHAR(10)		''	Central Index Key US specific
gics	VARCHAR(8)		''	Global Industry Classification Standard
lei	VARCHAR(20)		''	Legal Entity Identifier
naics	VARCHAR(6)		''	North American Industry Classification System
cfi	VARCHAR(6)		''	ISO Classification of Financial Instruments
cic	VARCHAR(4)		''	Complementay Identification Code
fisn	VARCHAR(40)		''	Financial Instrument Short Name
isin	VARCHAR(12)		''	ISIN code
figi	VARCHAR(16)		''	FIGI code
bbgCompositeTicker	VARCHAR(12)		''	Bloomberg Composite Ticker
bbgExchangeTicker	VARCHAR(28)		''	Bloomberg Exchange Ticker
bbgCompositeGlobalID	VARCHAR(12)		''	Bloomberg Composite Global ID
bbgGlobalID	VARCHAR(12)		''	Bloomberg Global ID
bbgCurrency	VARCHAR(3)		''	Bloomberg Trading Currency
otcPrimaryMarket	enum - OTCPrimaryMarket		'None'
otcTier	enum - OTCTier		'None'
otcReportingStatus	VARCHAR(1)		''
otcDisclosureStatus	INT		0
otcFlags	INT		0
stkPriceInc	enum - StkPriceInc		'None'	Price increment None FullPenny Nickle
tkDefSource	enum - TkDefSource		'None'	Ticker definition source None Vendor OTC SR Exchange
statusFlag	enum - TkStatusFlag		'None'
tapeCode	enum - TapeCode		'None'	SIP Tape Code
stkVolume	FLOAT		0	daily stock volume
futVolume	FLOAT		0	daily future volume
optVolume	FLOAT		0	daily option volume
exchString	VARCHAR(8)		''	exchanges listing any options on this underlying
hasOptions	enum - YesNo		'None'	Has Options flag
numOptions	INT		0	total number of listed options
timeMetric	enum - TimeMetric		'None'	trading time metric 252 or 365 trading days or a weekly cycle type
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
ticker_tk	1
ticker_at	2
ticker_ts	3

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgTickerDefinition` (
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `securityID` INT NOT NULL DEFAULT 0 COMMENT 'Security ID number from the source - Vendor, SR, Feed',
    `symbolType` ENUM('None','Equity','ADR','ETF','CashIndex','MutualFund','ShortETF','Future','Bond','DepReceipts','PreferredSec','PreferenceShare','StructuredProd','StapledSec','TradeableRights','Unit','Warrant','WhenIssued','ForeignIssue') NOT NULL DEFAULT 'None',
    `name` VARCHAR(72) NOT NULL DEFAULT '' COMMENT 'Symbol name',
    `country` VARCHAR(2) NOT NULL DEFAULT '' COMMENT 'ISO Issuer Country Code',
    `primaryCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `rateCurve` ENUM('None','Default','SOFR','SpxBox','StoxxBox') NOT NULL DEFAULT 'None',
    `parValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'Security Parvalue',
    `parValueCurrency` VARCHAR(3) NOT NULL DEFAULT '' COMMENT 'Security Parvalue currency',
    `pointValue` FLOAT NOT NULL DEFAULT 0,
    `pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `primaryExch` ENUM('None','NYSE','NYSEArca','NYSEMkt','NASDAQ','NASDAQBOS','BATS','PHLX','IEXG','CSE','NSE','FINRA','PORTAL','OTC','CME','CBOT','NYMEX','COMEX','ICE','EUREX','XETRA','CDEX','BXE','CXE','DXE','LSE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP') NOT NULL DEFAULT 'None',
    `altID` INT NOT NULL DEFAULT 0 COMMENT 'Alt Security ID number',
    `mic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'ISO Market Identification Code',
    `micSeg` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'ISO Market Indentification Segment Code',
    `symbol` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'trading symbol (w/o dot notation)',
    `issueClass` VARCHAR(1) NOT NULL DEFAULT '' COMMENT 'issue class of stock symbol.  if no issue class field will be blank.',
    `sharesOutstanding` BIGINT NOT NULL DEFAULT 0 COMMENT 'symbol shares outstanding, represented in thousands (actualsharesoutstanding = sharesoutstanding * 1000)',
    `cusip` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'cusip code',
    `indNum` INT NOT NULL DEFAULT 0 COMMENT 'IND (2 digits)',
    `subNum` INT NOT NULL DEFAULT 0 COMMENT 'SUB (4 digits)',
    `grpNum` INT NOT NULL DEFAULT 0 COMMENT 'GRP (6 digits)',
    `nbrNum` INT NOT NULL DEFAULT 0 COMMENT 'NBR (8 digits)',
    `sic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'SIC (Standard Industrial Classification) code',
    `cik` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'Central Index Key (US specific)',
    `gics` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'Global Industry Classification Standard',
    `lei` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Legal Entity Identifier',
    `naics` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'North American Industry Classification System',
    `cfi` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'ISO Classification of Financial Instruments',
    `cic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'Complementay Identification Code',
    `fisn` VARCHAR(40) NOT NULL DEFAULT '' COMMENT 'Financial Instrument Short Name',
    `isin` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'ISIN code',
    `figi` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'FIGI code',
    `bbgCompositeTicker` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Composite Ticker',
    `bbgExchangeTicker` VARCHAR(28) NOT NULL DEFAULT '' COMMENT 'Bloomberg Exchange Ticker',
    `bbgCompositeGlobalID` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Composite Global ID',
    `bbgGlobalID` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Global ID',
    `bbgCurrency` VARCHAR(3) NOT NULL DEFAULT '' COMMENT 'Bloomberg Trading Currency',
    `otcPrimaryMarket` ENUM('None','OTCLink','OTCBB','OTCLinkBB','GreyMarket','OTCBonds') NOT NULL DEFAULT 'None',
    `otcTier` ENUM('None','OtcNoTier','OTCQXUSPrem','OTCQXUS','OTCQXIntPrem','OTCQXInt','OTCQB','OTCBBOnly','PinkCurr','PinkLim','PinkNoInfo','Grey','Expert','OTCBonds') NOT NULL DEFAULT 'None',
    `otcReportingStatus` VARCHAR(1) NOT NULL DEFAULT '',
    `otcDisclosureStatus` INT NOT NULL DEFAULT 0,
    `otcFlags` INT NOT NULL DEFAULT 0,
    `stkPriceInc` ENUM('None','FullPenny','Nickle') NOT NULL DEFAULT 'None' COMMENT 'Price increment: None; FullPenny; Nickle',
    `tkDefSource` ENUM('None','Vendor','OTC','SR','Exchange') NOT NULL DEFAULT 'None' COMMENT 'Ticker definition source: None; Vendor; OTC; SR; Exchange',
    `statusFlag` ENUM('None','Active','Delisted') NOT NULL DEFAULT 'None',
    `tapeCode` ENUM('None','A','B','C') NOT NULL DEFAULT 'None' COMMENT 'SIP Tape Code',
    `stkVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily stock volume',
    `futVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily future volume',
    `optVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily option volume',
    `exchString` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'exchanges listing any options on this underlying',
    `hasOptions` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'Has Options flag',
    `numOptions` INT NOT NULL DEFAULT 0 COMMENT 'total number of listed options',
    `timeMetric` ENUM('None','D252','D365','SPX','WK1','WK2','WK3','WK4') NOT NULL DEFAULT 'None' COMMENT 'trading time metric - 252 or 365 trading days or a weekly cycle type',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='TickerDefinition (internal only) records exist for all SpiderRock tickers including equity tickers (stocks and ETFs) as well as index tickers and synthetic tickers for future chains and option multihedge baskets.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `securityID`,
    `symbolType`,
    `name`,
    `country`,
    `primaryCurrency`,
    `rateCurve`,
    `parValue`,
    `parValueCurrency`,
    `pointValue`,
    `pointCurrency`,
    `primaryExch`,
    `altID`,
    `mic`,
    `micSeg`,
    `symbol`,
    `issueClass`,
    `sharesOutstanding`,
    `cusip`,
    `indNum`,
    `subNum`,
    `grpNum`,
    `nbrNum`,
    `sic`,
    `cik`,
    `gics`,
    `lei`,
    `naics`,
    `cfi`,
    `cic`,
    `fisn`,
    `isin`,
    `figi`,
    `bbgCompositeTicker`,
    `bbgExchangeTicker`,
    `bbgCompositeGlobalID`,
    `bbgGlobalID`,
    `bbgCurrency`,
    `otcPrimaryMarket`,
    `otcTier`,
    `otcReportingStatus`,
    `otcDisclosureStatus`,
    `otcFlags`,
    `stkPriceInc`,
    `tkDefSource`,
    `statusFlag`,
    `tapeCode`,
    `stkVolume`,
    `futVolume`,
    `optVolume`,
    `exchString`,
    `hasOptions`,
    `numOptions`,
    `timeMetric`,
    `timestamp`
FROM `SRAnalytics`.`MsgTickerDefinition`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='TickerDefinition' ORDER BY ordinal_position ASC;